﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace CodeRobot.DAL
{
    /// <summary>
    /// 创建数据库帮助页面
    /// </summary>
    public class DocumentHelper
    {
        /// <summary>
        /// 创建项目数据库设计文档
        /// </summary>
        /// <param name="strAppName"></param>
        public static void CreateDataDesignDocumentForHTML(string strDBName, string strAppName)
        {
            try
            {
                // 生成HTML页面
                CodeRobot.Utility.IniFile iniFile = new Utility.IniFile(Application.StartupPath + "\\config.ini");
                string strDocumentPath = iniFile.GetString("DIR", "DOCUMENT", "");
                string strVersion = iniFile.GetString("COPYRIGHT", "VERSION", "");
                string strCode = iniFile.GetString("COPYRIGHT", "CODE", "");

                StreamWriter sw = new StreamWriter(strDocumentPath + "\\"+ strDBName.ToLower() + "_data_design.html", false, Encoding.GetEncoding("utf-8"));

                sw.WriteLine("<!DOCTYPE html>");
                sw.WriteLine("<html>");
                sw.WriteLine("<head>");
                sw.WriteLine("    <meta charset=\"utf-8\">");
                sw.WriteLine("    <meta http-equiv=\"X-UA-Compatible\" content=\"IE=edge\">");
                sw.WriteLine("    <title>DataDoc数据库设计文档</title>");
                sw.WriteLine("    <meta name=\"viewport\" content=\"initial-scale=1, maximum-scale=1\">");
                sw.WriteLine("    <link rel=\"shortcut icon\" href=\"/favicon.ico\">");
                sw.WriteLine("    <meta name=\"apple-mobile-web-app-capable\" content=\"yes\">");
                sw.WriteLine("    <meta name=\"apple-mobile-web-app-status-bar-style\" content=\"black\">");
                sw.WriteLine("</head>");
                sw.WriteLine("<body>");
                sw.WriteLine("    <div style=\"margin: 20px auto; text-align: center; width: 95%;\">");
                sw.WriteLine("        <div><h1>" + strAppName + "</h1></div>");
                sw.WriteLine("        <div><h3 style=\"color: #4d606e\">数据库设计文档</h3></div>");
                sw.WriteLine("        <div style=\"margin-bottom: 50px;\"></div>");
                sw.WriteLine("        <div class=\"txt\"><strong>数据库名：</strong> DHLS_TWZDTT</div>");
                sw.WriteLine("        <div class=\"txt\"><strong>数据库版本：</strong>MYSQL 8.0.20</div>");
                sw.WriteLine("        <div class=\"txt\"><strong>数据库引擎：</strong>InnoDB</div>");
                sw.WriteLine("        <div class=\"txt\"><strong>字符集：</strong>utf8mb4</div>");
                sw.WriteLine("        <div class=\"txt\"><strong>文档版本：</strong>V" + strVersion + "." + strCode + "</div>");
                sw.WriteLine("        <div class=\"txt\"><strong>文档描述：</strong>" + strAppName + "数据库设计文档</div>");
                sw.WriteLine("        <div class=\"txt\"><strong>编写日期：</strong>" + DateTime.Now.ToString("yyyy年M月d日") + "</div>");
                sw.WriteLine("        <div class=\"txt\" style=\"margin: 50px 0;\">");
                sw.WriteLine("            <table class=\"table-style\">");
                sw.WriteLine("                <tr>");
                sw.WriteLine("                    <td class=\"title\" style=\"width: 10%; background-color: #d3d4d8\">序号</td>");
                sw.WriteLine("                    <td class=\"title\" style=\"width: 30%; background-color: #d3d4d8\">表名</td>");
                sw.WriteLine("                    <td class=\"title\" style=\"width: 20%; background-color: #d3d4d8\">字符集</td>");
                sw.WriteLine("                    <td class=\"title\" style=\"width: 20%; background-color: #d3d4d8\">排序规则</td>");
                sw.WriteLine("                    <td class=\"title\" style=\"width: 20%; background-color: #d3d4d8\">说明</td>");
                sw.WriteLine("                </tr>");
                sw.WriteLine(GetTableList(strDBName));
                sw.WriteLine("            </table>");
                sw.WriteLine("        </div>");
                sw.WriteLine(GetTableColumnList(strDBName));
                sw.WriteLine("        <div></div>");
                sw.WriteLine("        <style type=\"text/css\">");
                sw.WriteLine("            a {");
                sw.WriteLine("                color: #3fbac2;");
                sw.WriteLine("                text-decoration: none;");
                sw.WriteLine("            }");
                sw.WriteLine("            .txt {");
                sw.WriteLine("                text-align: left;");
                sw.WriteLine("                margin: 10px;");
                sw.WriteLine("            }");
                sw.WriteLine("            .center {");
                sw.WriteLine("                text-align: center;");
                sw.WriteLine("            }");
                sw.WriteLine("            .table-style {");
                sw.WriteLine("                width: 100%;");
                sw.WriteLine("                border: 1px solid #e4eef7;");
                sw.WriteLine("                border-collapse: collapse;");
                sw.WriteLine("            }");
                sw.WriteLine("            tr {");
                sw.WriteLine("                height: 40px;");
                sw.WriteLine("            }");
                sw.WriteLine("            td {");
                sw.WriteLine("                border: 1px solid #f5f5f5;");
                sw.WriteLine("            }");
                sw.WriteLine("            .title {");
                sw.WriteLine("                padding: 10px;");
                sw.WriteLine("                text-align: center;");
                sw.WriteLine("                color: #000;");
                sw.WriteLine("            }");
                sw.WriteLine("            .value {");
                sw.WriteLine("                padding: 10px;");
                sw.WriteLine("                color: #666;");
                sw.WriteLine("                white-space: nowrap;");
                sw.WriteLine("                overflow: hidden;");
                sw.WriteLine("                text-overflow: ellipsis;");
                sw.WriteLine("            }");
                sw.WriteLine("        </style>");
                sw.WriteLine("    </div>");
                sw.WriteLine("</body>");
                sw.WriteLine("</html>");
                sw.Close();
            }
            catch (Exception ex)
            {
                CodeRobot.Utility.LogHelper.Error(typeof(ReadmeHelper), ex, "创建项目README文件", "CreateProjectFile",false);
            }
        }

        /// <summary>
        /// 获取项目所有表列表
        /// </summary>
        /// <param name="strDBName"></param>
        public static string GetTableList(string strDBName)
        {
            string returnValue = "";

            try
            {
                int num = 1;
                MySqlConnection cn = new MySqlConnection(CodeRobot.DBSqlHelper.DBMySQLHelper.ConnectionMySQL());
                cn.Open();
                string strSql = "SELECT TABLE_NAME,TABLE_COMMENT,ENGINE,TABLE_COLLATION FROM `information_schema`.`TABLES` WHERE TABLE_SCHEMA = '" + strDBName + "'";
                MySqlCommand cmd = new MySqlCommand(strSql, cn);
                MySqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    var table_name = dr["TABLE_NAME"].ToString();
                    var column_comment = dr["TABLE_COMMENT"].ToString();
                    var engine = dr["ENGINE"].ToString();
                    var table_collation = dr["TABLE_COLLATION"].ToString();

                    string strColumn = "                <tr>";
                    strColumn += "                    <td class=\"value center\">" + num + "</td>";
                    strColumn += "                    <td class=\"value\"><a href=\"#" + table_name + "\">" + table_name.ToUpper() + "</a></td>";
                    strColumn += "                    <td class=\"value center\">" + engine + "</td>";
                    strColumn += "                    <td class=\"value center\">" + table_collation + "</td>";
                    strColumn += "                    <td class=\"value\">" + column_comment + "</td>";
                    strColumn += "                </tr>";

                    returnValue += strColumn;
                    num++;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                CodeRobot.Utility.LogHelper.Error(typeof(CodeHelper), ex, "获取项目所有表清单", "GetTableList", false);
            }

            return returnValue;
        }

        /// <summary>
        /// 获取所有表中的字段列表
        /// </summary>
        /// <param name="strDBName"></param>
        public static string GetTableColumnList(string strDBName)
        {
            string returnValue = "";

            try
            {
                int num = 1;
                MySqlConnection cn = new MySqlConnection(CodeRobot.DBSqlHelper.DBMySQLHelper.ConnectionMySQL());
                cn.Open();
                string strSql = "SELECT TABLE_NAME,TABLE_COMMENT,ENGINE,TABLE_COLLATION FROM `information_schema`.`TABLES` WHERE TABLE_SCHEMA = '" + strDBName + "'";
                MySqlCommand cmd = new MySqlCommand(strSql, cn);
                MySqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    var table_name = dr["TABLE_NAME"].ToString();
                    var column_comment = dr["TABLE_COMMENT"].ToString();
                    var engine = dr["ENGINE"].ToString();
                    var table_collation = dr["TABLE_COLLATION"].ToString();

                    returnValue += GetTableColumnItemList(strDBName, table_name, column_comment);
                    num++;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                CodeRobot.Utility.LogHelper.Error(typeof(CodeHelper), ex, "获取项目所有表清单", "GetTableList", false);
            }

            return returnValue;
        }

        /// <summary>
        /// 获取表中的字段
        /// </summary>
        /// <param name="strDBName"></param>
        /// <param name="strTableName"></param>
        public static string GetTableColumnItemList(string strDBName, string strTableName,string strTableComment)
        {
            string returnValue = "";

            try
            {
                string strColumnList = "";
                int num = 1;
                MySqlConnection cn = new MySqlConnection(CodeRobot.DBSqlHelper.DBMySQLHelper.ConnectionMySQL());
                cn.Open();
                string strSql = "SELECT COLUMN_NAME,DATA_TYPE,COLUMN_TYPE,COLUMN_KEY,COLUMN_COMMENT,EXTRA,COLUMN_DEFAULT,CHARACTER_SET_NAME,IS_NULLABLE,NUMERIC_PRECISION,CHARACTER_MAXIMUM_LENGTH FROM `information_schema`.`COLUMNS` WHERE  TABLE_SCHEMA='" + strDBName + "' AND TABLE_NAME='" + strTableName + "' ORDER BY ordinal_position ";
                MySqlCommand cmd = new MySqlCommand(strSql, cn);
                MySqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    string strColumnName = dr["COLUMN_NAME"].ToString();//字段列名
                    string strDataType = dr["DATA_TYPE"].ToString();//字段类型
                    string strCharacterMaxinumLength = dr["CHARACTER_MAXIMUM_LENGTH"].ToString();//字段长度
                    string strNumericPrecision = dr["NUMERIC_PRECISION"].ToString();//小数位
                    string strColumnKey = dr["COLUMN_KEY"].ToString();//是否是主键
                    string strIsNullable = dr["IS_NULLABLE"].ToString();//是否为空
                    string strColumnDefault = dr["COLUMN_DEFAULT"].ToString();//默认值
                    string strColumnComment = dr["COLUMN_COMMENT"].ToString();//注释
                    strColumnComment = strColumnComment.Replace("{搜索}", "");
                    strColumnComment = strColumnComment.Replace("{下拉}", "");
                    strColumnComment = strColumnComment.Replace("{勾选}", "");

                    if (string.IsNullOrEmpty(strCharacterMaxinumLength))
                    {
                        strCharacterMaxinumLength = strNumericPrecision;
                    }

                    if (strIsNullable == "NO")
                    {
                        strIsNullable = "否";
                    }
                    else
                    {
                        strIsNullable = "是";
                    }

                    if (strColumnKey == "PRI")
                    {
                        strColumnKey = "是";
                    }
                    else
                    {
                        strColumnKey = "否";
                    }

                    string strColumn = "                <tr>";
                    strColumn += "                    <td class=\"value center\">" + num + "</td>";
                    strColumn += "                    <td class=\"value\">" + strColumnName + "</td>";
                    strColumn += "                    <td class=\"value center\">" + strDataType + "</td>";
                    strColumn += "                    <td class=\"value center\">" + strCharacterMaxinumLength + "</td>";
                    // strColumn += "                    <td class=\"value center\">" + strNumericPrecision + "</td>";
                    strColumn += "                    <td class=\"value center\">" + strIsNullable + "</td>";
                    strColumn += "                    <td class=\"value center\">" + strColumnKey + "</td>";
                    strColumn += "                    <td class=\"value center\">" + strColumnDefault + "</td>";
                    strColumn += "                    <td class=\"value\">" + strColumnComment + "</td>";
                    strColumn += "                </tr>";

                    strColumnList += strColumn;

                    num++;
                }
                dr.Dispose();
                cn.Close();

                string strTableColumn = "        <div class=\"txt\" style=\"margin: 150px 0;\">";
                strTableColumn += "            <div><a id=\"" + strTableName + "\"></a></div>";
                strTableColumn += "            <div class=\"txt\"><strong>表名：</strong><span style=\"color: #3fbac2\">" + strTableName.ToUpper() + "</span></div>";
                strTableColumn += "            <div class=\"txt\"><strong>说明：</strong>"+ strTableComment + "</div>";
                strTableColumn += "            <div class=\"txt\"><strong>数据列：</strong></div>";
                strTableColumn += "            <table class=\"table-style\">";
                strTableColumn += "                <tr>";
                strTableColumn += "                    <td class=\"title\" style=\"background-color: #d3d4d8\">序号</td>";
                strTableColumn += "                    <td class=\"title\" style=\"background-color: #d3d4d8\">字段</td>";
                strTableColumn += "                    <td class=\"title\" style=\"background-color: #d3d4d8\">类型</td>";
                strTableColumn += "                    <td class=\"title\" style=\"background-color: #d3d4d8\">长度</td>";
                // strTableColumn += "                    <td class=\"title\" style=\"background-color: #d3d4d8\">精度</td>";
                strTableColumn += "                    <td class=\"title\" style=\"background-color: #d3d4d8\">允许空值</td>";
                strTableColumn += "                    <td class=\"title\" style=\"background-color: #d3d4d8\">主键</td>";
                strTableColumn += "                    <td class=\"title\" style=\"background-color: #d3d4d8\">默认值</td>";
                strTableColumn += "                    <td class=\"title\" style=\"background-color: #d3d4d8\">说明</td>";
                strTableColumn += "                </tr>";
                strTableColumn += strColumnList;
                strTableColumn += "            </table>";
                strTableColumn += "        </div>";

                returnValue = strTableColumn;
            }
            catch (Exception ex)
            {
                CodeRobot.Utility.LogHelper.Error(typeof(CodeHelper), ex, "获取表中的字段", "GetTableColumnList", false);
            }

            return returnValue;
        }

    }
}
